package com.cskaoyan.jdbc.batch;

import com.cskaoyan.jdbc.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/05/13 11:24
 *
 * @author ciggar
 *
 * 批处理
 */
public class BatchDemo {

    // 主要是研究各种方式批处理的效率

    // for 循环

    // statement

    // preparedStatement

    // 主要是研究这三种方式的效率
    // 例如都插入1000条数据，对比一下效率



    static Connection connection;

    static {

        connection = JDBCUtils.getConnection();

    }


    public static void main(String[] args) throws SQLException {

        long t1 = System.currentTimeMillis();
        batchUseForEach();
        long t2 = System.currentTimeMillis();
        batchUseStatement();
        long t3 = System.currentTimeMillis();
        batchUsePrepapreStatement();
        long t4 = System.currentTimeMillis();

        System.out.println("for循环:" + (t2-t1) + " ms");
        System.out.println("statement批处理:" + (t3-t2) + " ms");
        System.out.println("PrepapreStatement批处理:" + (t4-t3) + " ms");

        /*
         1000 次的时间:

            for循环:1170 ms
            statement批处理:1140 ms
            PrepapreStatement批处理:957 ms

        10000次的时间：
            for循环:8689 ms
            statement批处理:9187 ms
            PrepapreStatement批处理:8660 ms


        添加了配置之后：rewriteBatchedStatements=true
            for循环:9395 ms
            statement批处理:8174 ms
            PrepapreStatement批处理:115 ms
         */

    }



    // for循环来做
    public static void batchUseForEach() throws SQLException {

        Statement statement = connection.createStatement();

        for (int i = 0; i < 10000; i++) {

            String sql = "insert into user values ("+i+",'foreach',null,null)";

            statement.executeUpdate(sql);

        }


    }


    // Statement 来处理
    public static void batchUseStatement() throws SQLException {


        Statement statement = connection.createStatement();


        for (int i = 10000; i < 20000; i++) {
            String sql = "insert into user values ("+i+",'batchUseStatement',null,null)";
            statement.addBatch(sql);
        }

        // 发送SQL语句，执行
        statement.executeBatch();


    }





    // PreparedStatement来处理
    public static void batchUsePrepapreStatement() throws SQLException {

        // 获取PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement("insert into user values (?,?,null,null)");


        // 循环，设置参数
        for (int i = 20000; i < 30000; i++) {

            preparedStatement.setInt(1,i);
            preparedStatement.setString(2,"PrepapreStatement");

            preparedStatement.addBatch();

        }

        // 把参数发送给MySQL服务器，执行SQL语句
        preparedStatement.executeBatch();

        // insert into user values (),(),(),(),();

    }







}
